package 链接snappydate数据库相关操作.snappydateDDL相关操作

import org.apache.log4j.Logger

import java.io.{FileWriter, PrintWriter}
import java.sql.{DriverManager, PreparedStatement, ResultSet}
import java.text.SimpleDateFormat
import java.util.Date
import scala.util.matching.Regex

class DatabaseDDL {
  def select_table_struct(): Unit = {
    val url: String = s"jdbc:snappydata://172.16.193.60:1527/"
    val conn = DriverManager.getConnection(url)
    val log: Logger = Logger.getLogger("Structure_change_ of_detection_table1.0")
    if (conn == null) {
      log.error("The database is not connect:\n" + conn)
    }
    val m_DBMetaData = conn.getMetaData
    val stmt = conn.createStatement()
    val catalog: String = conn.getCatalog
    val tablesResultSet: ResultSet = m_DBMetaData.getTables(catalog, null, null, Array[String]("TABLE"))

    //    val tablesResultSet: ResultSet = m_DBMetaData.getTables(null, "ops_monitor", "%", null)
    if (tablesResultSet == null) {
      log.error("The database is null ,should be the database not exit.")
    }
    while (tablesResultSet.next()) {
      val tableType: String = tablesResultSet.getString("TABLE_TYPE")
      val tableSchema = tablesResultSet.getString("TABLE_SCHEM")
      val tableName: String = tablesResultSet.getString("TABLE_NAME")
      //      log.info(s"Get tables in a specific database:$tableSchema")
      val sql3 = String.format("Desc extended " + tableSchema + "." + tableName + "") //查询sql
      val ps: PreparedStatement = null
      var clob1: String = null
      try {
        val ps = conn.prepareStatement(sql3)
        val resultSet: ResultSet = ps.executeQuery()
        while (resultSet.next()) {
          clob1 = resultSet.getString(2)
        }
      }
      //      println("clob1", clob1)
      val pattern = new Regex("transient_lastDdlTime=(\\d+)")
      val str = clob1
      val str1: String = (pattern findAllIn str).mkString(" ")
      val pattern1 = new Regex("[0-9]")
      val result: Long = (pattern1 findAllIn str1).mkString("").toLong * 1000
      val millis = Until.tranTimeToString(result)
      /** get Clob
       * */
      val sql = String.format("SHOW CREATE TABLE " + tableSchema + "." + tableName + "") //Second query SQL
      var clobtest: String = null
      try {
        val ps = conn.prepareStatement(sql)
        //ps.setString(1, tableName)
        val resultSet: ResultSet = ps.executeQuery()
        while (resultSet.next()) {
          clobtest = resultSet.getString(1)
        }
      }
      val clob = s"" + clobtest
        .replaceAll("\n", " ")
        .replaceAll("`", "")
        .replaceAll("'", "") + ""
      val stmt = conn.prepareStatement("INSERT INTO ops_monitor.audit_struct_table(schemaname,objectname,lastuptime) " +
        "VALUES('" + tableSchema + "','" + tableName + "','" + millis + "')")
      stmt.execute()
      stmt.close()

      /** Extract CLOB information and information from archive table lastdatatime */

      val sql4 = s"select DISTINCT objectname,schemaname ,objecttype,lastupdatedate,objectcreatescript,status from" +
        s" (select DISTINCT objectname,schemaname ,objecttype,lastupdatedate,objectcreatescript,status from  ops_monitor.audit_log where " +
        s"objectname='" + tableName + "' and schemaname='" + tableSchema + "' and objecttype='" + tableType + "')" +
        s" order by lastupdatedate desc limit 1"

      val pre = conn.prepareStatement(sql4)
      val rst = pre.executeQuery()
      if (!rst.next()) {
        log.info("*********************INSERT options2**************************")
        /** save ops_monitor.AUDIT_LOG */
        //        val date = new java.util.Date()
        val stmt = conn.prepareStatement("INSERT INTO ops_monitor.AUDIT_LOG(schemaname,objecttype,objectname,lastupdatedate," +
          "objectcreatescript,status) VALUES('" + tableSchema + "','" + tableType + "','" + tableName +
          "','" + millis +
          "','" + clob + "','add')")
        //        log.info(s"The add new tables,schemaname:${tableSchema},objecttype:${tableType},objectname:${tableName},lastupdatedate${millis} ")
        val simpdate = new Date()
        val simpleDateFormattime = new SimpleDateFormat("yyyy-MM-dd")
        val str: String = simpleDateFormattime.format(simpdate)
        val writer = new PrintWriter(new FileWriter(s"/tmp/MonitoringTableStructure/logs/${str}.log", true))
        writer.write(s"${simpdate} add new tables: schemaname: ${tableSchema},objecttype: ${tableType},objectname: ${tableName},lastupdatedate: ${millis}\n")
        writer.close()
        stmt.execute()
        stmt.close()

        /** save ops_monitor.AUDIT_LOG ,Save a few tables a few times */
        val pstmt = conn.prepareStatement("insert into ops_monitor.audit_metadata(schemaname," +
          "objectname,objecttype) values('" + tableSchema + "','" + tableName + "','" + tableType + "')")
        pstmt.execute
        pstmt.close()

      }
      else {
        val database_time = Until.tranTimeToLong(rst.getString("lastupdatedate"))
        val out_time = Until.tranTimeToLong(millis)
        val ddl_from_log = rst.getString("objectcreatescript")
        //          println(rst.getString("objectcreatescript"))

        if (database_time < out_time || ddl_from_log != clob) {
          /** save ops_monitor.AUDIT_LOG */
          if (tableSchema == "ops_monitor" && tableName == "audit_struct_table" && tableType == "table") {
            println("no do thing ,break it...")
          }
          else {
            val stmt = conn.prepareStatement("INSERT INTO ops_monitor.AUDIT_LOG(schemaname,objecttype,objectname,lastupdatedate," +
              "objectcreatescript,status) VALUES('" + tableSchema + "','" + tableType + "','" + tableName +
              "','" + millis +
              "','" + clob + "','update')")
            //            log.info(s"The Update tables:schemaname:${tableSchema},objecttype:${tableType},objectname:${tableName},lastupdatedate:${millis}")
            val simpdate = new Date()
            val simpleDateFormattime = new SimpleDateFormat("yyyy-MM-dd")
            val str: String = simpleDateFormattime.format(simpdate)
            val writer = new PrintWriter(new FileWriter(s"/tmp/MonitoringTableStructure/logs/${str}.log", true))
            writer.write(s"${simpdate}:update tables: schemaname: ${tableSchema},objecttype: ${tableType},objectname: ${tableName},lastupdatedate: ${millis}\n")
            writer.close()
            stmt.execute()
            stmt.close()
          }

        }
      }
      rst.close()

    }
    //        conn.close()
  }

}
